import requests
from lxml import etree
import time
import pymysql
import re

headers = {
    "user-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/131.0.0.0 Safari/537.36",
    "cookie": 'bid=j_95V11oMkE; douban-fav-remind=1; ll="118183"; __utmc=30149280; __utmz=30149280.1733904538.2.2.utmcsr=cn.bing.com|utmccn=(referral)|utmcmd=referral|utmcct=/; __utmc=223695111; __utmz=223695111.1733904542.1.1.utmcsr=cn.bing.com|utmccn=(referral)|utmcmd=referral|utmcct=/; _pk_id.100001.4cf6=b5f2d88df7a0b91b.1733904542.; _vwo_uuid_v2=D6197BD9AFFD6A6026FEA7A509970F9D2|0cd57782d676e09b52b9ed8c38a3f1fb; _pk_ref.100001.4cf6=%5B%22%22%2C%22%22%2C1733966104%2C%22https%3A%2F%2Fcn.bing.com%2F%22%5D; _pk_ses.100001.4cf6=1; __utma=30149280.126929866.1724983487.1733904538.1733966104.3; __utmb=30149280.0.10.1733966104; __utma=223695111.1754835362.1733904542.1733904542.1733966104.2; __utmb=223695111.0.10.1733966104; ap_v=0,6.0; dbcl2="267574031:BcVxW5/MvRw"; ck=2duE; push_noty_num=0; push_doumail_num=0'
}

# 1、创建数据库链接
with pymysql.connect(host="master", port=3306, database="bigdata31", user="root", password="123456") as con:
    # 2、获取游标,用于执行sql
    cursor = con.cursor()

    # 执行sql查询所有的id
    cursor.execute("select id,name from movies")

    # 取出所有的id
    ids = cursor.fetchall()

    # 循环爬取每个电影的评价
    for id, name in ids:
        for page in range(1, 11):
            print(f"正在爬取:{name}:的第{page}页评价")
            start = 20 * (page - 1)
            # 发起请求
            response = requests.get(
                url=f"https://movie.douban.com/subject/{id}/comments?start={start}&limit=20&status=P&sort=new_score",
                headers=headers)
            print(response.status_code)

            # 获取网站的源代码
            text = response.text

            # 解析源代码
            html = etree.HTML(text)

            # 获取所有的评价
            comments = html.xpath("//div[@class='comment']")

            # 循环解析每一个评价
            for comment in comments:
                user = "|".join(comment.xpath("h3/span[@class='comment-info']/a/text()"))

                score = "|".join(comment.xpath("h3/span[@class='comment-info']/span[2]/@class"))
                # 使用正则匹配获取评分
                score = "|".join(re.findall("[1-9]", score))
                # 评价时间
                comment_time = "|".join(
                    comment.xpath("h3/span[@class='comment-info']/span[@class='comment-time ']/text()")).strip()
                # 评价内容
                comment_content = "|".join(comment.xpath("p[@class=' comment-content']/span/text()")).replace("\n",
                                                                                                              "").replace(
                    "\r", "")

                cursor.execute("""insert into comments values(%s,%s,%s,%s,%s,%s)""",
                               [id, name, user, score, comment_time, comment_content])

                con.commit()
